package com.iris.live.services.common;

import com.iris.live.services.common.reconsitution.BIExtractColumnMetaData;
import com.iris.live.services.common.reconsitution.BIExtractMetaData;

import java.util.ArrayList;
import java.util.List;

/**
 * KPI工具类
 */
public class BIExtractStatements {
    private static BIExtractStatements statements = new BIExtractStatements();
    protected List<BIExtractMetaData> metas = new ArrayList<>();

    private BIExtractStatements() {
        buildStatements();
    }

    public static BIExtractStatements getInstance() {
        return statements;
    }

    private void buildStatements() {
        //新车外部销售收入（OK）， 当月开票销售收入 - 当月退票销售收入 (CHECKED)
        String newCarSaleVolumn = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.许可_LSH, a.经销商名,a.经销商名_EN, " +
                " a.salesCode, b.来源, b.品牌, b.级别,  新车销量 新车销量, DCC销售台数, 展厅销售台数, 批发销售台数, 集团外调拨销售台数,大客户销售台数, 集团内调拨销售台数 " +
                " FROM LSH经销商表 a LEFT JOIN ( " +
                "       SELECT  " +
                "           a.`许可`, d.来源, d.品牌, d.级别," +
                "           SUM(开票类型 * IF(LEFT(e.销售类型,2) IN ('B','I'), 0, 1) ) 新车销量, " +
                "           SUM(开票类型 * IF(LEFT(e.销售类型,2) = 'R1', 1, 0)) DCC销售台数, " +
                "           0 展厅销售台数, " +
                "           0 批发销售台数, " +
                "           SUM(开票类型 * IF(LEFT(e.销售类型,2) = 'O:', 1, 0)) 集团外调拨销售台数,  " +
                "           SUM(开票类型 * IF(LEFT(e.销售类型,2) = 'F:', 1, 0)) 大客户销售台数,  " +
                "           SUM(开票类型 * IF(LEFT(e.销售类型,2) = 'I:', 1, 0)) 集团内调拨销售台数 " +
                "       FROM vw_retail_invoice_info a, `lsh车型` d, `报价订单表` e  " +
                "       WHERE a.`车型编号` = d.`车型编号` and a.订单编号 = e.订单编号 " +
                "       AND a.`处理时间` >= CONCAT('2000-01-01',' 00:00:01') " +
                "       AND a.`处理时间` <= CONCAT('2020-01-01',' 23:59:59') " +
                "       AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END) " +
                "       GROUP BY a.`许可`, d.来源, d.品牌, d.级别 " +
                " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'";
        {
            metas.add(new BIExtractMetaData("newCarSaleVolumn", "新车销量", newCarSaleVolumn).addExtractColumnMetaData(new BIExtractColumnMetaData("新车销量", "vehicle_sale_volumn"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("展厅销售台数", "exhibition_invoice_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("DCC销售台数", "dcc_invoice_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("批发销售台数", "wholesale_invoice_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("集团外调拨销售台数", "outside_group_invoice_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("大客户销售台数", "key_account_invoice_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("集团内调拨销售台数", "inside_group_invoice_num"))

            );
        }

        //新车外部销售收入（OK）， 当月开票销售收入 - 当月退票销售收入 (CHECKED)
        String newCarSaleAmount = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.许可_LSH,a.salesCode, a.经销商名,a.经销商名_EN, " +
                " b.来源, b.品牌, b.级别,  SUM(TYPE * (车辆销售价未税 - 消费税未税)) 新车外部销售收入 " +
                " FROM LSH经销商表 a LEFT JOIN ( " +
                "       SELECT  " +
                "           a.开票类型 TYPE, " +
                "           a.`许可`, d.来源, d.品牌, d.级别, " +
                "           IFNULL(a.折后价, 0) / 1.17 车辆销售价未税, " +
                "           IFNULL(a.消费税, 0) / 1.17 消费税未税 " +
                "       FROM vw_retail_invoice_info a, `lsh车型` d, `报价订单表` e  " +
                "       WHERE a.`车型编号` = d.`车型编号` and a.订单编号 = e.订单编号 " +
                "       AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "       AND LEFT(e.`销售类型`,1) NOT IN ('B','D','I','P') " +
                "       AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END) " +
                " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' " +
                " GROUP BY 许可_BMBS, b.来源, b.品牌, b.级别 ";
        metas.add(new BIExtractMetaData("newCarSaleAmount", "新车外部销售收入", newCarSaleAmount)
                .addExtractColumnMetaData(new BIExtractColumnMetaData("新车外部销售收入", "vehicle_sale_amount"))
        );


        // BMBS新车销量
        String bmbsSaleVolumn = "SELECT allset.*, IFNULL(a.新车销量,0) as 新车销量  " +
                " FROM kpi_allset allset " +
                "LEFT JOIN " +
                "( " +
                "   SELECT  a.`所属经销商` AS permit,c.来源,c.品牌,c.级别,COUNT(1) AS 新车销量 " +
                "   FROM  lsh库存表 a,  库存利润表 b,  lsh车型 c " +
                "   WHERE  b.`上报厂家` = '是' " +
                "      AND DATE_FORMAT(b.`上报厂家日期`,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d')  " +
                "      AND DATE_FORMAT(b.`上报厂家日期`,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') " +
                "      AND a.`库存编号` = b.`库存编号` " +
                "      AND a.`车型编号` = c.`车型编号` " +
                "      AND a.is_delete = 0 " +
                "      AND a.`车架号` NOT LIKE 'IRIS%' " +
                "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END) " +
                "   GROUP BY a.`所属经销商`,c.来源,c.品牌,c.级别 " +
                " ) a   " +
                "ON a.permit=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别` ";
        {
            metas.add(new BIExtractMetaData("bmbsSaleVolumn", "BMBS新车销量", bmbsSaleVolumn).addExtractColumnMetaData(new BIExtractColumnMetaData("新车销量", "manufacturer_vehicle_sale_volumn")));
        }

        // BMBS销售目标完成(BMBS RT TA OK) (CHECKED)
        String vehicleSaleTarget = "SELECT allset.*, IFNULL(b.bmbs_销售目标,0) as 厂家销售目标, IFNULL(lsh_销售目标,0) as 经销商销售目标  " +
                " FROM kpi_allset allset " +
                "LEFT JOIN  " +
                "( " +
                "    SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(bmbs_ta,0)) as bmbs_销售目标, sum(IFNULL(lsh_ta,0)) as lsh_销售目标 " +
                "    FROM snp_setting a    " +
                "    join LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'    " +
                "    WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))    " +
                "    AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))    " +
                "    and (case when 'MB' = '' then true else b.品牌 = 'MB' end)   " +
                "    group by a.permit,b.`来源`,b.`品牌`,b.`级别` " +
                ") b  " +
                "ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`";
        {
            metas.add(new BIExtractMetaData("vehicleSaleTarget", "新车销售目标", vehicleSaleTarget).addExtractColumnMetaData(new BIExtractColumnMetaData("厂家销售目标", "manufacturer_sale_target"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("经销商销售目标", "dealer_sale_target"))
            );
        }

        // 二手车评估率(OK)
        String usedCarAppraisal = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, b.来源, b.品牌, b.级别, " +
                "   二手车评估台数" +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "       SELECT 许可, 来源, 品牌, 级别, SUM(TYPE * 二手车评估台数) 二手车评估台数 " +
                "       FROM ( " +
                "	       SELECT a.许可, b.来源, b.品牌, b.级别, " +
                "              a.开票类型 TYPE, " +
                "              1 二手车评估台数 " +
                "	        FROM vw_retail_invoice_info a, LSH车型 b, 报价置换表 c " +
                "	       WHERE a.车型编号 = b.车型编号  and a.置换编号 = c.置换编号  AND c.置换估价 > 0 " +
                "          AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE B.品牌 = 'MB' END)  " +
                "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
                "  ) B ON a.许可_BMBS = b.许可   where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("usedCarAppraisal", "二手车评估", usedCarAppraisal).addExtractColumnMetaData(new BIExtractColumnMetaData("二手车评估台数", "used_car_estimation_num")));
        }

        //金融费用 单车金融手续费收入（OK）04金融战败  09金融通过后撤单, CHECKED
        String financialInfo = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
                "     a.salesCode, a.来源,a.品牌, a.级别,  " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融) 零售金融台数, " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=3, 1, 0)) 厂商金融台数, " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=2, 1, 0)) 银行金融台数, " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=1, 1, 0)) DCH金融台数, " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * VEHICLE_FINANCE_INFO.金融手续费) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 金融手续费 , " +

                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=1, 1, 0) * VEHICLE_FINANCE_INFO.金融手续费) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 DCH金融手续费 , " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=2, 1, 0) * VEHICLE_FINANCE_INFO.金融手续费) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 银行金融手续费 , " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IF(金融类型=3, 1, 0) * VEHICLE_FINANCE_INFO.金融手续费) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 厂家金融手续费 , " +

                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * VEHICLE_FINANCE_INFO.金融服务费) 金融服务费,    " +
                "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IFNULL(VEHICLE_FINANCE_INFO.贷款金额,0)) 金融贷款额, " +
                "     IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 手续费品牌调整  " +
                " FROM kpi_allset a  " +
                " LEFT JOIN (" +
                "      SELECT  a.许可, d.来源,d.品牌,d.级别, " +
                "              a.开票类型 TYPE, " +
                "              LSH金融机构设置.type 金融类型,   " +
                "              IF(C.状态 NOT IN ('04','09') AND B.金融机构 IS NOT NULL AND B.贷款额 > 0 , 1, 0) 是否金融, " +
                "              IFNULL(a.折后价,0) - IFNULL(b.首付额,0) 贷款金额, " +
                "              IFNULL(b.金融服务费,0)/1.17 金融服务费, " +
                "              (IFNULL(b.金融手续费,0) + IFNULL(e.finance_poundage_adjust,0))/1.06 金融手续费 " +
                "      FROM vw_retail_invoice_info a " +
                "      LEFT JOIN (" +
                "               SELECT PERMIT, STOCK_CODE, FINANCE_POUNDAGE_ADJUST  " +
                "               FROM INCOME_ADJUSTMENT" +
                "               WHERE TYPE = 1 " +
                "      ) e ON A.许可 = e.permit and a.库存编号 = e.stock_code,   " +
                "      报价金融表 b LEFT JOIN  (SELECT DISTINCT 金融机构, TYPE FROM LSH金融机构设置) LSH金融机构设置 ON b.`金融机构` = LSH金融机构设置.`金融机构`," +
                "      LSH金融申请 c, `lsh车型` d " +
                "      WHERE  a.金融 = b.金融编号 " +
                "            and a.初始报价单号 = c.初始报价单号 and A.车型编号 = d.车型编号 " +
                "            AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                " ) VEHICLE_FINANCE_INFO ON a.许可_BMBS = VEHICLE_FINANCE_INFO.许可 AND a.来源 = VEHICLE_FINANCE_INFO.来源 AND a.品牌 = VEHICLE_FINANCE_INFO.品牌 AND  a.级别 = VEHICLE_FINANCE_INFO.级别 " +
                " LEFT JOIN (" +
                "      SELECT PERMIT, 来源, 品牌, 级别," +
                "             SUM(finance_poundage_adjust) finance_poundage_adjust " +
                "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "            AND '2000-01-01' <> '2020-01-01' " +
                "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
                " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                " LEFT JOIN (" +
                "      SELECT PERMIT, 来源, 品牌,   " +
                "             SUM(finance_poundage_adjust) finance_poundage_adjust " +
                "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "            AND '2000-01-01' <> '2020-01-01' " +
                "      GROUP BY PERMIT, 来源, 品牌  " +
                " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
        {
            metas.add(new BIExtractMetaData("financialInfo", "金融数据", financialInfo).addExtractColumnMetaData(new BIExtractColumnMetaData("零售金融台数", "financial_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("DCH金融台数", "dch_financial_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("银行金融台数", "bank_financial_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("厂商金融台数", "factory_financial_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("金融手续费", "手续费品牌调整", "financial_commission_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("DCH金融手续费", "dch_financial_service_fee_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("银行金融手续费", "bank_financial_service_fee_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("厂家金融手续费", "factory_financial_service_fee_amount"))

                    .addExtractColumnMetaData(new BIExtractColumnMetaData("金融服务费", "financial_service_fee_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("金融贷款额", "financial_load_amount"))
            );
        }

        //Agility渗透率 OR 先享后选渗透率(OK)
        String agilityNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
                "  a.salesCode, 来源, b.品牌, b.级别, b.新车Agility台数 " +
                "  FROM LSH经销商表 a  LEFT JOIN (  " +
                "     SELECT a.许可, 来源, a.品牌, a.级别,   " +
                "            SUM(TYPE * a.新车Agility台数) 新车Agility台数" +
                "     FROM (  " +
                "             SELECT  a.许可, 来源, d.品牌, d.级别, a.车型编号, a.开票时间,   " +
                "                     a.开票类型 TYPE,   " +
                "                     1 新车台数,  " +
                "                     IF(C.状态 NOT IN ('04','09') && B.金融机构 ='奔驰租赁' AND B.贷款额 > 0, 1, 0) 新车Agility台数  " +
                "             FROM vw_retail_invoice_info a    " +
                "             LEFT JOIN  报价金融表 b ON a.金融 = b.`金融编号`" +
                "             LEFT JOIN LSH金融申请 c ON a.初始报价单号 = c.初始报价单号,  LSH车型 d   " +
                "             WHERE a.车型编号 = d.车型编号   " +
                "               AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "               AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)    " +
                "     ) a   " +
                "     GROUP BY a.许可,来源, 品牌, a.级别   " +
                " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("agilityNum", "新车Agility台数", agilityNum).addExtractColumnMetaData(new BIExtractColumnMetaData("新车Agility台数", "new_agility_vehicle_num")));
        }

        //SI金额（OK）  销售管理报表(CHECKED)
        String siAmount = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
                "  a.salesCode, Z.来源,z.级别,z.品牌,  " +
                "  ROUND(z.SI,2) as SI金额, SI品牌调整值  " +
                "  FROM LSH经销商表 a LEFT JOIN (      " +
                "             select          " +
                "                  SUM(type* (厂家随车SI + 贴息金额 + `零售SI` + `批发SI`))  " +
                "                  + IFNULL(VEHICLE_MODEL_BMBS_调整.随车调整值,0) " +
                "                  + IFNULL(VEHICLE_MODEL_贴息_调整.贴息调整值,0) " +
                "                  + IFNULL(VEHICLE_MODEL_SI_调整.R_SI_AMOUNT,0) " +
                "                  + IFNULL(VEHICLE_MODEL_SI_调整.W_SI_AMOUNT,0)  AS SI,        " +
                "                  IFNULL(VEHICLE_BRAND_BMBS_调整.随车调整值,0) " +
                "                  + IFNULL(VEHICLE_BRAND_贴息_调整.贴息调整值,0) " +
                "                  + IFNULL(VEHICLE_BRAND_SI_调整.R_SI_AMOUNT,0) " +
                "                  + IFNULL(VEHICLE_BRAND_SI_调整.W_SI_AMOUNT,0)  AS SI品牌调整值,        " +
                "                  e.许可,e.来源, e.品牌, e.级别 " +
                "             from (  " +
                "                     SELECT       " +
                "                            IFNULL(IFNULL(VEHICLE_BMBS_调整值.随车调整值 ,f.厂家随车SI),0) AS 厂家随车SI,    " +
                "                            IF(IFNULL(VEHICLE_SI_调整.车辆贴息调整值,0) = 0, IFNULL(lsh金融申请附属.贴息金额, 0), VEHICLE_SI_调整.车辆贴息调整值) AS 贴息金额,  " +
                "                            IFNULL(IFNULL(IFNULL(VEHICLE_SI_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
                "                            IFNULL(IF(b.所属经销商 = b.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`,  " +
                "                            b.许可, d.来源, d.品牌,d.级别 ,  " +
                "                            b.开票类型 type  " +
                "                     FROM vw_retail_invoice_info b    " +
                "                     LEFT JOIN `库存利润表` z  on b.`库存编号` = z.`库存编号`  " +
                //"                   -- 贴息金额  " +
                "                     LEFT JOIN lsh金融申请附属  ON B.初始报价单号 = lsh金融申请附属.初始报价单号         " +
                "                     LEFT JOIN (  " +
                //"                      -- 厂家随车SI，包括购置税  " +
                "                         SELECT SUM(随车成本) AS 厂家随车SI, 报价编号 FROM 报价随车表            " +
                "                         WHERE 随车类型 = '厂家'  GROUP BY 报价编号            " +
                "                     ) f ON f.报价编号 = b.报价编号          " +
                "                     LEFT JOIN (" +
                "                        SELECT A.STOCK_ID, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
                "                        FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C " +
                "                        WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家'" +
                "                        GROUP BY STOCK_ID " +
                "                     ) VEHICLE_BMBS_调整值 ON VEHICLE_BMBS_调整值.STOCK_ID = b.库存编号  " +
                "                     LEFT JOIN (  " +
                "                       SELECT PERMIT, STOCK_ID, " +
                "                          SUM(IF(SI_NAME_CN = '零售SI', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "                          SUM(IF(SI_NAME_CN = '批售SI', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "                          SUM(IF(SI_NAME_CN = '购置税', SI_AMOUNT, 0)) PT_SI_AMOUNT, " +
                "                          SUM(IF(SI_NAME_CN = '贴息', SI_AMOUNT, 0)) 车辆贴息调整值 " +
                "                       FROM SI_BMBS_SETTING A , SI_SETTING B " +
                "                       WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID " +
                "                       GROUP BY PERMIT, STOCK_ID " +
                "                    ) VEHICLE_SI_调整 ON VEHICLE_SI_调整.PERMIT =b.许可 AND VEHICLE_SI_调整.STOCK_ID = b.库存编号 " +
                "                    LEFT JOIN (   " +
                //"                 -- 预估零售SI,预估批售SI  " +
                "                        SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI  " +
                "                        FROM snp_setting  WHERE y_m = '190001'   " +
                "                    ) snp_setting ON snp_setting.carCode = b.车型编号 and snp_setting.permit=b.许可  " +
                "                    ,LSH车型 d  " +
                "                    WHERE b.车型编号 = d.车型编号  " +
                "                     AND (date_format(b.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "                     AND (CASE WHEN 'MB' = '' THEN TRUE ELSE 品牌 = 'MB' END)   " +
                "             )e  " +
                "             LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
                "                 FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 D  " +
                "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别   " +
                "            ) VEHICLE_MODEL_BMBS_调整 ON e.许可 = VEHICLE_MODEL_BMBS_调整.PERMIT AND e.来源 = VEHICLE_MODEL_BMBS_调整.来源 AND e.品牌 = VEHICLE_MODEL_BMBS_调整.品牌  AND e.级别 = VEHICLE_MODEL_BMBS_调整.级别" +
                "             LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, SUM(A.SI_AMOUNT)  贴息调整值 " +
                "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
                "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = D.车型编号 AND D.级别 IS NOT NULL AND A.SI_BMBS_CODE = 'P' " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别  " +
                "            ) VEHICLE_MODEL_贴息_调整 ON e.许可 = VEHICLE_MODEL_贴息_调整.PERMIT AND e.来源 = VEHICLE_MODEL_贴息_调整.来源 AND e.品牌 = VEHICLE_MODEL_贴息_调整.品牌  AND e.级别 = VEHICLE_MODEL_贴息_调整.级别" +
                "             LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, " +
                "                        SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "                        SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "                        SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
                "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
                "                 WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND D.级别 IS NOT NULL " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别   " +
                "            ) VEHICLE_MODEL_SI_调整 ON e.许可 = VEHICLE_MODEL_SI_调整.PERMIT AND e.来源 = VEHICLE_MODEL_SI_调整.来源 AND e.品牌 = VEHICLE_MODEL_SI_调整.品牌  AND e.级别 = VEHICLE_MODEL_SI_调整.级别" +

                "            LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
                "                 FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 D  " +
                "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌   " +
                "            ) VEHICLE_BRAND_BMBS_调整 ON e.许可 = VEHICLE_BRAND_BMBS_调整.PERMIT AND e.来源 = VEHICLE_BRAND_BMBS_调整.来源 AND e.品牌 = VEHICLE_BRAND_BMBS_调整.品牌" +
                "             LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌, SUM(A.SI_AMOUNT)  贴息调整值 " +
                "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
                "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_BMBS_CODE = 'P' " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌  " +
                "            ) VEHICLE_BRAND_贴息_调整 ON e.许可 = VEHICLE_BRAND_贴息_调整.PERMIT AND e.来源 = VEHICLE_BRAND_贴息_调整.来源 AND e.品牌 = VEHICLE_BRAND_贴息_调整.品牌" +
                "             LEFT JOIN (  " +
                "                 SELECT A.PERMIT, D.来源, D.品牌,  " +
                "                        SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "                        SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "                        SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
                "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
                "                 WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "                    AND '2000-01-01' <> '2020-01-01' " +
                "                 GROUP BY A.PERMIT, D.来源, D.品牌   " +
                "            ) VEHICLE_BRAND_SI_调整 ON e.许可 = VEHICLE_BRAND_SI_调整.PERMIT AND e.来源 = VEHICLE_BRAND_SI_调整.来源 AND e.品牌 = VEHICLE_BRAND_SI_调整.品牌" +
                "          GROUP BY e.许可,e.来源, e.品牌, e.级别     " +
                "  ) z  ON a.许可_BMBS = z.许可 where a.`是否测试` = '否'";
        {
            metas.add(new BIExtractMetaData("siAmount", "SI金额", siAmount).addExtractColumnMetaData(new BIExtractColumnMetaData("SI金额", "SI品牌调整值", "si_amount")));
        }

        // 采购批发数  批发日期为在此区间的车辆台数 (OK)  销售管理报表
        String stockPurchaseNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, 来源,  b.品牌, b.级别,b.实际采购" +
                "  FROM LSH经销商表 a LEFT JOIN (" +
                "  SELECT lsh库存表.`采购批发商` as 许可,  来源, " +
                "   `lsh车型`.`品牌`," +
                "   `lsh车型`.`级别`," +
                "   COUNT(1) 实际采购   " +
                "  FROM  `lsh库存表`   " +
                "  JOIN  `lsh车型`  ON  `lsh库存表`.`车型编号` = `lsh车型`.`车型编号` " +
                "  WHERE `lsh库存表`.`车架号` NOT LIKE 'IRIS%'  AND `lsh库存表`.is_delete<> '1'  " +
                "    AND date_format(`lsh库存表`.`批发日期`,'%Y-%m-%d')>='2000-01-01'" +
                "    AND date_format(`lsh库存表`.`批发日期`,'%Y-%m-%d')<='2020-01-01'" +
                "  group by 来源, `lsh车型`.品牌, `lsh车型`.级别,lsh库存表.`采购批发商`) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("stockPurchaseNum", "采购批发数", stockPurchaseNum).addExtractColumnMetaData(new BIExtractColumnMetaData("实际采购", "purchase_num")));
        }

        //SC  人数    (OK  销售管理)
        String scNumber = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  b.销售顾问人数, 'N/A' 来源, b.品牌, 'N/A' 级别  " +
                "FROM LSH经销商表 a LEFT JOIN (  " +
                "   select count(1) AS  销售顾问人数, 许可, 品牌    " +
                "   from (" +
                "       select a.许可, substring_index(substring_index(a.`品牌`,';',b.help_topic_id+1),';',-1) 品牌 " +
                "       from  `用户` a join mysql.help_topic b on b.help_topic_id < (length(a.品牌) - length(replace(a.品牌,';',''))+1)  " +
                "       WHERE 角色='销售顾问'   " +
                "        AND (`职位`<>'实习' OR `职位` IS NULL) " +
                "        AND (`删除状态` <>'已删除' or `删除状态` is NULL) " +
                "   ) a" +
                "   where a.品牌 IS NOT NULL AND a.品牌<>'' " +
                "   GROUP BY 许可, 品牌 ) B on a.许可_BMBS = b.许可 where a.`是否测试` = '否' AND 品牌 IS NOT NULL ";
        {
            metas.add(new BIExtractMetaData("scNumber", "销售顾问人数", scNumber).addExtractColumnMetaData(new BIExtractColumnMetaData("销售顾问人数", "sc_num"))
            );
        }

        // 库存（OK）(CHECKED)
        String stockNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,  " +
                "  if(b.180天库龄 is null,0 ,180天库龄) 180天库龄,  " +
                "  if(b.150天库龄 is null,0 ,150天库龄) 150天库龄,  " +
                "  if(b.120天库龄 is null,0 ,120天库龄) 120天库龄,  " +
                "  if(b.90天_150天库龄 is null,0 ,90天_150天库龄) 90天_150天库龄,  " +
                "  if(b.90天库龄 is null,0 ,90天库龄) 90天库龄,  " +
                "  if(b.0_90天库龄 is null,0 ,0_90天库龄) 0_90天库龄,  " +
                "  if(b.60天库龄 is null,0 ,60天库龄) 60天库龄,  " +
                "  if(b.总库存 is null,0 ,总库存) 总库存  " +
                " FROM lsh经销商表 a  LEFT JOIN (   " +
                "     SELECT a.许可, 来源, a.品牌, a.级别,SUM(180天库龄) 180天库龄, SUM(150天库龄) 150天库龄, SUM(120天库龄) 120天库龄, SUM(90天_150天库龄) 90天_150天库龄," +
                "            SUM(90天库龄) 90天库龄, SUM(0_90天库龄) 0_90天库龄, SUM(60天库龄) 60天库龄, SUM(总库存) 总库存 " +
                "     FROM ( " +
                "	      SELECT  b.所属经销商  as 许可, 来源, a.品牌, a.级别, IF(transfer.stock_code IS NULL, 1, 0) 总库存, " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 180, 1 , 0) 180天库龄,  " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 150 && transfer.stock_code IS NULL, 1 , 0) 150天库龄,  " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 120 && DATEDIFF('2020-01-01', 批发日期) < 150 && transfer.stock_code IS NULL, 1 , 0) 120天库龄,  " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 150 && transfer.stock_code IS NULL, 1 , 0) 90天_150天库龄,  " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 120 && transfer.stock_code IS NULL, 1 , 0) 90天库龄, " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) < 90 && transfer.stock_code IS NULL, 1 , 0) 0_90天库龄, " +
                "                IF(DATEDIFF('2020-01-01', 批发日期) >= 60 && DATEDIFF('2020-01-01', 批发日期) < 90 && transfer.stock_code IS NULL, 1 , 0) 60天库龄 " +
                "	      FROM   LSH车型 a, LSH库存表 b left join (select a.库存编号, max(b.开票时间) 开票时间, max(b.退票时间)  退票时间  " +
                "                                                 from 报价单表 a, 报价交车表 b " +
                "                                                 where a.交车 = b.编号 and a.`是否还原点` = '是' and b.开票时间 <= '2018-01-31 23:59:59' " +
                "                                                 group by  a.库存编号 " +
                "                                                ) c on b.库存编号 = c.库存编号  " +
                "                                      LEFT JOIN car_transfer_log transfer on transfer.stock_code = b.库存编号 and transfer.stock_out_date > '2020-01-01' " +
                "         WHERE b.批发日期 <= '2020-01-01' and b.is_delete = 0 and b.车架号 not like 'IRIS%' and a.车型编号 = b.车型编号   " +
                "          and  (c.开票时间 IS NULL or (c.开票时间 < c.退票时间 and c.退票时间 <= '2018-01-31 23:59:59'))" +
                "	      AND (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
                "           UNION ALL " +
                "       SELECT  transfer.out_dealer  as 许可, 来源, a.品牌, a.级别, 1 总库存, " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 180, 1 , 0) 180天库龄,  " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 150, 1 , 0) 150天库龄,  " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 120 && DATEDIFF('2020-01-01', 批发日期) < 150, 1 , 0) 120天库龄,  " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 150, 1 , 0) 90天_150天库龄,  " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 120, 1 , 0) 90天库龄, " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) < 90 , 1 , 0) 0_90天库龄, " +
                "         IF(DATEDIFF('2020-01-01', 批发日期) >= 60 && DATEDIFF('2020-01-01', 批发日期) < 90, 1 , 0) 60天库龄 " +
                "       FROM    LSH车型 a, LSH库存表 b , car_transfer_log transfer " +
                "       WHERE   b.批发日期 <= '2020-01-01' and b.is_delete = 0 and b.车架号 not like 'IRIS%' and a.车型编号 = b.车型编号 " +
                "         and transfer.stock_code = b.库存编号 and transfer.stock_out_date > '2020-01-01' " +
                "	      AND (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
                "	  ) a group by a.许可, 来源, a.品牌, a.级别   " +
                " ) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("stockNum", "库存", stockNum).addExtractColumnMetaData(new BIExtractColumnMetaData("总库存", "stock_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("180天库龄", "age_larger_180"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("150天库龄", "age_larger_150"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("120天库龄", "age_larger_120"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("90天_150天库龄", "age_other_1"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("90天库龄", "age_larger_90"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("0_90天库龄", "age_other_2"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("60天库龄", "age_larger_60"))
            );
        }

        // 库存状态（OK）(CHECKED)
        String stockStatus = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,  " +
                "  if(b.在途已配 is null,0 ,在途已配) 在途已配,  " +
                "  if(b.在途可售 is null,0 ,在途可售) 在途可售,  " +
                "  if(b.在库可售 is null,0 ,在库可售) 在库可售,  " +
                "  if(b.在库已开票未提车 is null,0 ,在库已开票未提车) 在库已开票未提车,  " +
                "  if(b.在库已配 is null,0 ,在库已配) 在库已配,  " +
                "  if(b.提前销售已配 is null,0 ,提前销售已配) 提前销售已配,  " +
                "  if(b.提前销售纯虚 is null,0 ,提前销售纯虚) 提前销售纯虚  " +
                " FROM lsh经销商表 a  LEFT JOIN (   " +
                "     SELECT a.许可, 来源, a.品牌, a.级别, SUM(在途已配) 在途已配, SUM(在途可售) 在途可售, SUM(在库可售) 在库可售," +
                "            SUM(在库已配) 在库已配, SUM(在库已开票未提车) 在库已开票未提车,SUM(提前销售已配) 提前销售已配, SUM(提前销售纯虚) 提前销售纯虚 " +
                "     FROM ( " +
                "	      SELECT  b.所属经销商  as 许可, 来源, a.品牌, a.级别, " +
                "                IF((b.NBI车辆状态='在途库存' OR 入库日期 < '2020-01-01') AND 订单审批通过日期  <= '2020-01-01', 1 , 0)  在途已配,  " +
                "                IF((b.NBI车辆状态='在途库存' OR 入库日期 < '2020-01-01') AND (订单审批通过日期 IS NULL OR 订单审批通过日期 >= '2020-01-01'), 1 , 0)  在途可售,  " +
                "                IF(b.NBI车辆状态='现货库存' AND (订单审批通过日期 IS NULL OR  订单审批通过日期 >= '2020-01-01'), 1 , 0)  在库可售,  " +
                "                IF(b.NBI车辆状态='现货库存' AND  订单审批通过日期  <= '2020-01-01', 1 , 0)  在库已配,  " +
                "                IF(b.NBI车辆状态='现货库存' AND c.开票时间 <= '2020-01-01' AND (C.交车日期 IS NULL || C.交车日期 > '2020-01-01'), 1 , 0)  在库已开票未提车,  " +
                "                IF(D.上报厂家日期 <= 订单审批通过日期 AND 订单审批通过日期  <= '2020-01-01', 1 , 0)  提前销售已配,  " +
                "                IF((D.上报厂家日期 IS NOT NULL AND 订单审批通过日期 IS NULL) OR  (订单审批通过日期>= '2020-01-01' AND D.上报厂家日期 <= 订单审批通过日期), 1 , 0)  提前销售纯虚  " +
                "	      FROM   LSH车型 a, LSH库存表 b" +
                "         LEFT JOIN  库存利润表 d  ON b.库存编号 = d.库存编号" +
                "         LEFT JOIN vw_stock_quotation c ON B.库存编号 = c.库存编号" +
                "	      WHERE b.is_delete = 0 and b.车架号 not like 'IRIS%' and a.车型编号 = b.车型编号  " +
                "	      AND (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
                "	  ) a group by a.许可, 来源, a.品牌, a.级别   " +
                " ) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("stockStatus", "库存状态", stockStatus).addExtractColumnMetaData(new BIExtractColumnMetaData("在途已配", "onway_stock_allocation_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("在途可售", "onway_stock_saleable_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("在库可售", "onhand_stock_saleable_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("在库已配", "onhand_stock_allocation_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("在库已开票未提车", "onhand_stock_deliverable_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("提前销售已配", "presale_allocation_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("提前销售纯虚", "presale_fake_num"))
            );
        }

        // 新保台数 + 新保手续费收入 + 新保渗透率(OK)(CHECKED)
        String saleInsurance =
                "SELECT   " +
                        "  allset.*, IFNULL(SUM(   " +
                        "   CASE   " +
                        "   WHEN a.jq_order_date>= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.ins_type = 0 THEN   " +
                        "    0   " +
                        "   WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.is_ins_other != 2 THEN   " +
                        "    1   " +
                        "   WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.ins_type = 0 THEN   " +
                        "    - 1   " +
                        "   END   " +
                        "  ),0) jq_count ,   " +
                        "  IFNULL(SUM(   " +
                        "   CASE   " +
                        "   WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.ins_type = 1 THEN   " +
                        "    0   " +
                        "   WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.is_ins_other != 2 THEN   " +
                        "    1   " +
                        "   WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "   AND a.ins_type = 1 THEN   " +
                        "    - 1   " +
                        "   END   " +
                        "  ),0)  新保台数 ,   " +
                        "  IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 0 THEN   " +
                        "     0   " +
                        "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     1   " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 0 THEN   " +
                        "     - 1   " +
                        "    END   " +
                        "   ) * a.jq_fee   " +
                        "  ),0)  +   " +
                        "  IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 1 THEN   " +
                        "     0   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     1   " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 1 THEN   " +
                        "     - 1   " +
                        "    END   " +
                        "   ) * a.sy_fee   " +
                        "  ),0) +    " +
                        "  IFNULL(SUM(a.ins_adjust),0) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.06 保险手续费 ,  " +
                        "  IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.06 品牌保险手续费调整 ,  " +
                        "  SUM(   " +
                        "     " +
                        "    CASE   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other = 2 THEN   " +
                        "     0   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other = 2 THEN   " +
                        "     1   " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other = 2 THEN   " +
                        "     - 1   " +
                        "    END   " +
                        "  ) 新车销量 , " +
                        " IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     a.首年交强险保费   " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 0 THEN   " +
                        "     a.首年交强险保费*-1   " +
                        "    END   " +
                        "   )   " +
                        "  ),0) 首年交强险保费,   " +
                        " IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     a.交强险总保费   " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 0 THEN   " +
                        "     a.交强险总保费*-1   " +
                        "    END   " +
                        "   )   " +
                        "  ),0) 交强险总保费,   " +
                        "  IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     a.首年商业险保费 " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 1 THEN   " +
                        "     a.首年商业险保费*- 1   " +
                        "    END   " +
                        "   )    " +
                        "  ),0) 首年商业险保费,    " +
                        "  IFNULL(SUM(   " +
                        "   (   " +
                        "    CASE   " +
                        "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.is_ins_other != 2 THEN   " +
                        "     a.商业险总保费 " +
                        "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                        "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                        "    AND a.ins_type = 1 THEN   " +
                        "     a.商业险总保费*- 1   " +
                        "    END   " +
                        "   )    " +
                        "  ),0) 商业险总保费    " +
                        " FROM   " +
                        "  kpi_allset AS allset   " +
                        " LEFT JOIN kpi_ins_main AS a ON a.`permit` = allset.`许可_BMBS`   AND a.`来源` = allset.`来源` AND a.`品牌` = allset.`品牌` AND a.`级别` = allset.`级别` " +
                        " LEFT JOIN (" +
                        "      SELECT PERMIT, 来源, 品牌, 级别,   " +
                        "             SUM(insurance_poundage_adjust) insurance_poundage_adjust  " +
                        "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                        "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                        "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                        "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                        "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
                        " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON allset.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND allset.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND allset.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  allset.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                        " LEFT JOIN (" +
                        "      SELECT PERMIT, 来源, 品牌,   " +
                        "             SUM(insurance_poundage_adjust) insurance_poundage_adjust " +
                        "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                        "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                        "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                        "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                        "      GROUP BY PERMIT, 来源, 品牌  " +
                        " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON allset.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND allset.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND allset.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                        " WHERE CASE WHEN 'MB' = '' THEN TRUE ELSE allset.品牌 = 'MB' END  " +
                        " GROUP BY   " +
                        "  allset.`许可_BMBS` ,   " +
                        "  allset.`来源` ,   " +
                        "  allset.`品牌` ,   " +
                        "  allset.`级别` ";
        {
            metas.add(new BIExtractMetaData("saleInsurance", "新保", saleInsurance).addExtractColumnMetaData(new BIExtractColumnMetaData("新保台数", "new_insurance_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("保险手续费", "品牌保险手续费调整", "insurance_commission_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("商业险总保费", "new_insurance_commercial_premium_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("交强险总保费", "new_insurance_forced_premium_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("首年商业险保费", "commercial_insurance_first_year_premium"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("首年交强险保费", "forced_insurance_first_year_premium"))
            );
        }

        // GP（OK）(CHECKED)
        String saleGP = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
                "   a.salesCode, a.来源,a.品牌, a.级别,   " +
                "   ROUND(SUM(TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 - VEHICLE_GP.采购价格未税)),2) GP1, " +

                "   SUM(VEHICLE_GP.TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 - VEHICLE_GP.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税))) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  GP1dot5, " +
                "   (0- IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17)  GP1dot5_品牌调整值, " +

                " ROUND(SUM(VEHICLE_GP.TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 -VEHICLE_GP.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税  + 厂家随车赠送成本 +  贴息金额)))  " +
                " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 " +
                " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17 " +
                " - IFNULL(VEHICLE_MODEL_SI_BMBS_调整.厂家随车赠送成本调整值,0) - IFNULL(VEHICLE_MODEL_SI_BMBS_调整.车辆贴息调整值,0),1) GP2, " +

                " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 " +
                " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17 " +
                " - IFNULL(VEHICLE_BRAND_SI_BMBS_调整.厂家随车赠送成本调整值,0) + " +
                " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.车辆贴息调整值,0) GP2_品牌调整值, " +

                " ROUND(SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) + 厂家随车购置税 + 零售SI + 批发SI )) " +
                " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 " +
                " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0),2) AS GP3, " +
                " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                " - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 " +
                " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) AS GP3_品牌调整值, " +

                "   ROUND(IFNULL(SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) +  厂家随车购置税 + 零售SI + 批发SI + 精品毛利未税 + 置换评估费未税 + 延保毛利未税 + 其他服务费未税 + 上牌服务费未税 + 金融服务费未税 + 金融手续费未税 + 车辆保险手续费调整未税 + 报价预收其他毛利)),0)  " +
                "   + IFNULL(报价保险.交强险手续费,0) / 1.06 " +
                "   + IFNULL(报价保险.商业险手续费,0) / 1.06 " +
                "   + IFNULL(ins_var_fee.VCI_fee,0) " +
                "   + IFNULL(ins_var_fee.TCI_fee,0) " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17,2)   AS tgp, " +
                "   ROUND(IFNULL(SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税  + 精品销售价格未税 + 延保销售价格未税 + 置换评估费未税 + 其他服务费未税 + 上牌服务费未税  + 金融服务费未税 + 金融手续费未税 + 车辆保险手续费调整未税 + 报价预收其他毛利_2)),0) " +
                "   + IFNULL(报价保险.交强险手续费,0) / 1.06 " +
                "   + IFNULL(报价保险.商业险手续费,0) / 1.06 " +
                "   + IFNULL(ins_var_fee.VCI_fee,0) " +
                "   + IFNULL(ins_var_fee.TCI_fee,0) " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0),2) AS 销售毛收入,   " +

                "   0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0) /1.17 " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17  AS tgp_品牌调整值, " +
                "   IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) /1.17 AS 销售毛收入调整值 " +
                "   FROM kpi_allset a " +
                "   LEFT JOIN (   " +
                "       SELECT a.许可,  来源, g.品牌, g.级别, 销售类型,   " +
                "           a.开票类型 TYPE,   " +
                "           IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,        " +
                "           IFNULL(a.消费税,0) / 1.17 AS 消费税未税,        " +
                "           case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税,   " +
                "           IFNULL(GET_BMBS_AMOUNT(A.报价编号, A.库存编号),0) AS 厂家随车赠送成本, " +
                "           IFNULL(d.经销商赠送成本,0) AS 经销商赠送成本未税,        " +
                "           IFNULL(g.延保赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 AS 延保赠送成本未税,        " +
                "           IFNULL(f.精品赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_adjust, 0)/1.17 AS 精品赠送成本未税,      " +
                "		    IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.PT_SI_AMOUNT,IFNULL(d.厂家随车购置税, 0)), 0) AS 厂家随车购置税,      " +
                "           IFNULL(i.金融服务费,0) / 1.17 AS 金融服务费未税,        " +
                "           IFNULL(i.金融手续费,0) / 1.06 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.06 AS 金融手续费未税,        " +
                "           IFNULL(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17 AS 车辆保险手续费调整未税,        " +
                "           IFNULL(k.置换手续费,0) / 1.06 AS 置换评估费未税,        " +
                "           IFNULL(g.延保毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust, 0)/1.17AS 延保毛利未税,        " +
                "           IFNULL(g.延保销售价格, 0) / 1.17 AS 延保销售价格未税,        " +
                "           IFNULL(f.精品毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 精品毛利未税,      " +
                "           IFNULL(f.精品销售价格, 0) / 1.17 AS 精品销售价格未税,   " +
                "		    IFNULL(VEHICLE_SI_BMBS_调整.车辆贴息调整值, IFNULL(lsh金融申请附属.贴息金额, 0)) AS 贴息金额,    " +
                "           IFNULL(IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
                "           IFNULL(IF(a.所属经销商 = a.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`,      " +
                "           IFNULL(h.其他服务费,0) / 1.17 AS 其他服务费未税,        " +
                "           IFNULL(h.上牌服务费, 0) / 1.17 AS 上牌服务费未税,      " +
                "           IFNULL(报价预收其他毛利_2, 0) / 1.17 AS 报价预收其他毛利_2,      " +
                "           IFNULL(报价预收其他毛利,0)/1.17 AS 报价预收其他毛利  " +
                "       FROM vw_retail_invoice_info a    " +
                "       LEFT JOIN 库存利润表 z on a.库存编号 = z.库存编号 " +
                "       LEFT JOIN (            " +
                "         SELECT 报价编号," +
                "                SUM(IF(随车类型 = '经销商' AND 随车部门 = '销售部', 随车成本, 0)) AS 经销商赠送成本, " +
                "                SUM(IF(随车类型 = '厂家' AND 随车名称 <> '购置税', 随车成本, 0)) AS 厂家随车SI成本, " +
                "                SUM(IF(随车类型 = '厂家' AND 随车名称 = '购置税', 随车成本, 0)) AS 厂家随车购置税 " +
                "         FROM 报价随车表 GROUP BY 报价编号  " +
                "       ) d ON d.报价编号 = a.报价编号 " +
                "       LEFT JOIN (        " +
                "         SELECT  编号, " +
                "                 SUM(IF(折后价 = 0, 成本 * 数量, 0)) 精品赠送成本,   " +
                "                 SUM(IF(折后价 > 0, `折后价` * 数量, 0)) 精品销售价格,   " +
                "                 SUM(IF(折后价 > 0, `折后价` * 数量 - 成本 * 数量, 0)) 精品毛利 " +
                "           FROM 报价精品详细表 GROUP BY  编号  " +
                "       ) f ON f.编号 = a.精品        " +
                "       LEFT JOIN (          " +
                "         SELECT 延保编号, " +
                "                IF(IFNULL(厂家延保实收,0) = 0,厂家延保MSRP,0) +  IF(IFNULL(延保折后价,0) = 0,延保原价,0) + IF(IFNULL(第三方延保实收,0) = 0,第三方延保MSRP,0) AS 延保赠送成本,      " +
                "                IFNULL(第三方延保实收,0) + IFNULL(厂家延保实收,0) AS 延保销售价格,      " +
                "                IF(IFNULL(第三方延保实收,0) > 0, IFNULL(第三方延保实收,0) - IFNULL(第三方延保MSRP,0), 0) + if(IFNULL(厂家延保实收,0) > 0, IFNULL(厂家延保实收,0) - IFNULL(厂家延保MSRP,0), 0) AS 延保毛利      " +
                "         FROM 报价五星延保      " +
                "       ) g ON g.延保编号 = a.延保编号          " +
                "       LEFT JOIN (          " +
                "          SELECT 预收编号, IFNULL(其他一,0) + IFNULL(其他二,0) + IFNULL(其他三,0) + IFNULL(其他四,0) + IFNULL(其他五,0) - IFNULL(其他一成本,0) - IFNULL(其他二成本,0)  报价预收其他毛利, " +
                "                 IFNULL(其他一,0) + IFNULL(其他二,0) + IFNULL(其他三,0) + IFNULL(其他四,0) + IFNULL(其他五,0)  报价预收其他毛利_2 " +
                "          FROM 报价预收表 " +
                "       ) 报价预收 ON 报价预收.预收编号 = a.预收编号 " +
                "       LEFT JOIN (        " +
                "         SELECT 费用编号, " +
                "         SUM(IF(费用名称 = '其他服务费', 费用折后价,0)) AS 其他服务费,        " +
                "         SUM(IF(费用名称 = '上牌服务费', 费用折后价,0) - IF(费用名称 = '上牌服务费成本', 费用原价,0)) AS 上牌服务费        " +
                "         FROM 报价费用详细表  " +
                "         GROUP BY 费用编号        " +
                "       ) h ON h.费用编号 = a.费用编号      " +
                "       LEFT JOIN 报价金融表 i ON i.金融编号 = a.金融        " +
                "       LEFT JOIN lsh金融申请附属  ON a.初始报价单号 = lsh金融申请附属.初始报价单号         " +
                "       LEFT JOIN 报价置换表 k ON k.置换编号 = a.置换编号  " +
                "       LEFT JOIN (  " +
                "           SELECT PERMIT, STOCK_CODE,   " +
                "                  SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "                  SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "                  SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "                  SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "                  SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_adjust,   " +
                "                  SUM(IF(IS_GIVE=0, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_buy_adjust   " +
                "           FROM INCOME_ADJUSTMENT   " +
                "           WHERE TYPE = 1  " +
                "           GROUP BY PERMIT, STOCK_CODE " +
                "        ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
                "        LEFT JOIN (  " +
                "           SELECT PERMIT, STOCK_ID, " +
                "                  SUM(IF(SI_NAME_CN = '零售SI', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "                  SUM(IF(SI_NAME_CN = '批售SI', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "                  SUM(IF(SI_NAME_CN = '购置税', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
                "                  SUM(IF(SI_NAME_CN = '贴息', SI_AMOUNT, 0)) 车辆贴息调整值 " +
                "           FROM SI_BMBS_SETTING A , SI_SETTING B    " +
                "           WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID  " +
                "           GROUP BY PERMIT, STOCK_ID   " +
                "        ) VEHICLE_SI_BMBS_调整 ON VEHICLE_SI_BMBS_调整.PERMIT =a.许可 AND VEHICLE_SI_BMBS_调整.STOCK_ID = a.库存编号 " +
                "       LEFT JOIN (   " +
                "           SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI  " +
                "           FROM snp_setting  WHERE y_m = '190001'   " +
                "       ) snp_setting ON snp_setting.carCode = a.车型编号 and snp_setting.permit=a.许可   " +
                "       ,LSH车型 g , 报价订单表 h     " +
                "       WHERE a.订单编号 = h.订单编号 " +
                "          AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "          AND (CASE WHEN 'MB' = '' THEN TRUE ELSE g.品牌 = 'MB' END)    " +
                "          AND a.车型编号 = g.车型编号   " +
                "   ) VEHICLE_GP ON a.许可_BMBS = VEHICLE_GP.许可 AND a.来源 = VEHICLE_GP.来源 AND a.品牌 = VEHICLE_GP.品牌 AND  a.级别 = VEHICLE_GP.级别 " +
                "   LEFT JOIN (" +
                "        SELECT b.许可, d.来源, d.品牌, d.级别 ,IF(a.类型='0',SUM(a.poundage),0) TCI_fee, IF(a.类型='1',SUM(a.poundage),0) VCI_fee " +
                "        FROM lsh保险申请表附属_详细 a, 报价单表 b, vw_max_quotedcode_all c, LSH车型 d  " +
                "        where 批单日期 >= '2000-01-01' and 批单日期 <= '2020-01-01'    " +
                "        and a.初始报价单号 = b.初始报价单号 and b.车型编号 = d.车型编号 and b.报价编号 = c.报价编号 " +
                "        GROUP BY b.许可, d.来源, d.品牌, d.级别  " +
                "   ) ins_var_fee ON a.许可_BMBS = ins_var_fee.许可 AND a.来源 = ins_var_fee.来源 AND a.品牌 = ins_var_fee.品牌 AND  a.级别 = ins_var_fee.级别 " +
                "   LEFT JOIN (" +
                "       SELECT b.许可, e.来源, e.品牌, e.级别, sum(d.商业险手续费) 商业险手续费, sum(d.交强险手续费) 交强险手续费 " +
                "       FROM LSH保险申请附属 a, 报价单表 B, vw_max_quotedcode_all c, 报价保险表 d, `lsh车型` e " +
                "       WHERE A.初始报价单号 = b.初始报价单号 and b.报价编号 = c.报价编号 AND b.保险编号 = d.保险编号 and b.车型编号 = e.车型编号 " +
                "        and a.商业险出单日期 >= '2000-01-01'  " +
                "        and a.商业险出单日期 <= '2020-01-01' " +
                "       group by b.许可, e.来源, e.品牌, e.级别   " +
                "   ) 报价保险 ON a.许可_BMBS = 报价保险.许可 and a.来源 = 报价保险.来源 and a.品牌 = 报价保险.品牌 and a.级别 = 报价保险.级别" +
                "   LEFT JOIN (" +
                "        SELECT PERMIT, 来源, 品牌, 级别,   " +
                "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
                "        FROM INCOME_ADJUSTMENT, LSH车型   " +
                "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "          AND ADJUST_DATE >= '2000-01-01'  " +
                "          AND ADJUST_DATE <= '2020-01-01' " +
                "          AND '2000-01-01' <> '2020-01-01' " +
                "        GROUP BY PERMIT, 来源, 品牌, 级别  " +
                "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                "   LEFT JOIN (" +
                "        SELECT PERMIT, 来源, 品牌,   " +
                "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
                "        FROM INCOME_ADJUSTMENT, LSH车型   " +
                "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "          AND ADJUST_DATE >= '2000-01-01'  " +
                "          AND ADJUST_DATE <= '2020-01-01' " +
                "          AND '2000-01-01' <> '2020-01-01' " +
                "        GROUP BY PERMIT, 来源, 品牌  " +
                "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                "   LEFT JOIN (  " +
                "        SELECT PERMIT, 来源, 品牌, 级别, " +
                "               SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "               SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "               SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
                "               0 厂家随车赠送成本调整值, " +
                "               0 车辆贴息调整值   " +
                "        FROM SI_BMBS_SETTING, LSH车型    " +
                "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (车型 IS NULL OR 车型 ='') " +
                "            AND VALID_START_DATE >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "            AND VALID_START_DATE <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "            AND '2000-01-01' <> '2020-01-01' " +
                "        GROUP BY PERMIT, 来源, 品牌, 级别   " +
                "   ) VEHICLE_MODEL_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_MODEL_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_MODEL_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_MODEL_SI_BMBS_调整.品牌  AND a.级别 = VEHICLE_MODEL_SI_BMBS_调整.级别" +
                "   LEFT JOIN (  " +
                "        SELECT PERMIT, 来源, 品牌, " +
                "              SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "              SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "              SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
                "               0 厂家随车赠送成本调整值, " +
                "               0 车辆贴息调整值   " +
                "        FROM SI_BMBS_SETTING, LSH车型    " +
                "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (级别 IS NULL OR 级别 ='') " +
                "           AND VALID_START_DATE >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "           AND VALID_START_DATE <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "           AND '2000-01-01' <> '2020-01-01' " +
                "        GROUP BY PERMIT, 来源, 品牌   " +
                "   ) VEHICLE_BRAND_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_BRAND_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_BRAND_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_BRAND_SI_BMBS_调整.品牌  " +
                "  WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                "  GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
        {
            metas.add(new BIExtractMetaData("saleGP", "GP", saleGP).addExtractColumnMetaData(new BIExtractColumnMetaData("tgp", "tgp_品牌调整值", "tgp_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("销售毛收入", "销售毛收入调整值", "vehicle_sale_gross_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("gp3", "gp3_品牌调整值", "gp3_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("gp2", "gp2_品牌调整值", "gp2_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("GP1dot5", "GP1dot5_品牌调整值", "GP1dot5_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("GP1", "GP1_amount"))
            );
        }

        // 本地上牌率(KO)
        String saleLicense = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,   来源, b.品牌, b.级别,  " +
                "          sum(   CASE  " +
                "                 WHEN b.开票类型 = 1 AND   (b.上牌类型='本地摇号/拍牌' OR b.上牌类型='本地有号牌' )  THEN 1 " +
                "                 WHEN b.开票类型 = -1 AND   (b.上牌类型='本地摇号/拍牌' OR b.上牌类型='本地有号牌' )  THEN -1   ELSE 0 END ) 本地上牌的新车台数  " +
                "            from lsh经销商表 a left outer join ( select b.*, c.上牌类型,  来源, d.品牌, d.级别       " +
                "                                                 from vw_retail_invoice_info b , 报价客户表 c, LSH车型 d     " +
                "                                                 where b.`客户详细` = c.编号     " +
                "												  and b.`车型编号` = d.车型编号 and  (case when 'MB' = '' then true else d.品牌 = 'MB' end)    " +
                "                                                 and b.车辆零售状态 = '已卖'  " +
                "                                                 and b.退车日期 is null  " +
                "                                                 AND (date_format(b.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                "                                              ) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否'    " +
                "            group by a.`许可_BMBS`,  b.来源, b.品牌,b.级别    ";
        {
            metas.add(new BIExtractMetaData("saleLicense", "上牌", saleLicense).addExtractColumnMetaData(new BIExtractColumnMetaData("本地上牌的新车台数", "license_num")));
        }

        //新签订单
        String submitOrder = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,   " +
                " sum(b.TYPE) 新签订单数 " +
                " from lsh经销商表 a left join ( " +
                " select sum(CASE " +
                "            WHEN date_format(b.订单签署日期,'%Y-%m-%d') >= '2000-01-01'  and date_format(b.订单签署日期,'%Y-%m-%d')  <= '2020-01-01'   " +
                "                 AND  date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单失销日期,'%Y-%m-%d')  <= '2020-01-01'   " +
                "            THEN 0 " +
                "            WHEN date_format(b.订单签署日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单签署日期,'%Y-%m-%d') <='2020-01-01' THEN 1  " +
                "            END) as TYPE, " +
                "            b.许可 , 来源, d.品牌,d.级别 " +
                "  from  VW_ORDER_QUOTATION  b  " +
                "  left join LSH车型 d on b.`车型编号` = d.车型编号    " +
                "  where ((date_format(b.订单签署日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单签署日期,'%Y-%m-%d') >= '2000-01-01')  " +
                "     OR ((date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01')))       " +
                "    and (case when 'MB' = '' then true else d.品牌 = 'MB' end)  " +
                "    group by b.许可 , 来源, d.品牌,d.级别       " +
                "  ) b on a.`许可_BMBS` = b.许可  where a.`是否测试` = '否'  " +
                "  group by a.`许可_BMBS`,  b.来源, b.品牌,b.级别    ";
        {
            metas.add(new BIExtractMetaData("submitOrder", "新签订单数", submitOrder).addExtractColumnMetaData(new BIExtractColumnMetaData("新签订单数", "submit_order_num"))
            );
        }

        String saleOrder = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "   新增订单数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "      SELECT   " +
            "        a.`许可`,  " +
            "        car.`来源`,  " +
            "        car.`品牌`,  " +
            "        car.`级别`,  " +
            "        SUM(IF(a.`是否订单`='是',1,0))+SUM(IF(a.`是否订单`='退订',-1,0)) `新增订单数`  " +
            "      FROM `顾客进店记录表` a   " +
            "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
            "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
            "      WHERE a.`进店日期` >=  '2000-01-01'  AND a.`进店日期` <=  '2020-01-01'   " +
            // "      AND IFNULL(`停留时间`, 0 ) > 0   " +
            "      AND cust.`顾客姓名`!=''  " +
            "      AND cust.`电话号码`!=''  " +
            "      AND car.`车型`!=''  " +
            "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE car.品牌 = 'MB' END)  " +
            "      GROUP BY a.`许可` , car.`来源` , car.`品牌` , car.`级别`" +
            "   ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";
            {
                    metas.add(new BIExtractMetaData("saleOrder", "新增订单数", saleOrder).addExtractColumnMetaData(new BIExtractColumnMetaData("新增订单数", "new_order_num"))

                    );
            }

        // 销售订单(OK)
        String saleOrderDetail = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,   " +
                " sum(订单金融台数) as  '订单金融台数',  " +
                " sum(TYPE) 新增订单数," +
                " sum(展厅订单台数) 展厅订单台数, " +
                " sum(DCC订单台数) DCC订单台数, " +
                " sum(批发订单台数) 批发订单台数,    " +
                " sum(集团外调拨订单台数) 集团外调拨订单台数, " +
                " sum(大客户订单台数) 大客户订单台数, " +
                " sum(集团内调拨销售台数) 集团内调拨销售台数 " +
                " from lsh经销商表 a left join ( " +
                " select SUM(B.TYPE) as TYPE, " +
                "        SUM(B.TYPE * IF(c.金融手续费 is not null  AND `贷款额` > 0, 1, 0)) 订单金融台数, " +
                "        SUM(B.TYPE * IF(LEFT(b.销售类型,2) = 'R1', 1, 0)) 展厅订单台数, " +
                "        SUM(0) DCC订单台数, " +
                "        SUM(0) 批发订单台数, " +
                "        SUM(B.TYPE * IF(LEFT(b.销售类型,2) = 'O:', 1, 0)) 集团外调拨订单台数, " +
                "        SUM(B.TYPE * IF(LEFT(b.销售类型,2) = 'F:', 1, 0)) 大客户订单台数, " +
                "        SUM(B.TYPE * IF(LEFT(b.销售类型,2) = 'I:', 1, 0)) 集团内调拨销售台数, " +
                "        b.许可 , 来源, d.品牌,d.级别 " +
                "  from  (" +
                "       select CASE " +
                "            WHEN date_format(订单审批通过日期,'%Y-%m-%d') >= '2020-01-01'  and date_format(订单审批通过日期,'%Y-%m-%d')  <= '2020-01-01'   " +
                "                 AND  date_format(订单失销日期,'%Y-%m-%d') >= '2000-01-01' and date_format(订单失销日期,'%Y-%m-%d')  <= '2020-01-01'   " +
                "            THEN 0 " +
                "            WHEN date_format(订单审批通过日期,'%Y-%m-%d') >= '2000-01-01' and date_format(订单审批通过日期,'%Y-%m-%d') <='2020-01-01' THEN 1  " +
                "            WHEN date_format(订单失销日期,'%Y-%m-%d') >= '2000-01-01'  and  date_format(订单失销日期,'%Y-%m-%d') <= '2020-01-01' THEN -1  " +
                "          END type, A.*" +
                "     from VW_ORDER_QUOTATION A" +
                "     where  ((date_format(订单审批通过日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(订单审批通过日期,'%Y-%m-%d') >= '2000-01-01')  " +
                "     OR ((date_format(订单失销日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(订单失销日期,'%Y-%m-%d') >= '2000-01-01')))" +
                "  ) b  " +
                "  LEFT JOIN 报价金融表 c ON B.金融 = c.金融编号 " +
                "  left join LSH车型 d on b.`车型编号` = d.车型编号    " +
                "  where case when 'MB' = '' then true else d.品牌 = 'MB' end " +
                "  group by b.许可 , 来源, d.品牌,d.级别       " +
                "  ) b on a.`许可_BMBS` = b.许可  where a.`是否测试` = '否'  " +
                "  group by a.`许可_BMBS`,  b.来源, b.品牌,b.级别    ";
        {
            metas.add(new BIExtractMetaData("saleOrderDetail", "明细订单数", saleOrderDetail)
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("订单金融台数", "financial_vehicle_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("展厅订单台数", "exhibition_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("DCC订单台数", "dcc_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("批发订单台数", "wholesale_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("集团外调拨订单台数", "outside_group_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("集团内调拨销售台数", "inside_group_order_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("大客户订单台数", "key_account_order_num"))
            );
        }

        // 累计可开票订单数(OK)
        String totalInvoicableUnfulfilledOrderWithExistStock =
                "SELECT allset.*,IFNULL(a.累计可开票订单数,0) AS 累计可开票订单数 FROM   " +
                        " kpi_allset allset  " +
                        "LEFT JOIN   " +
                        "(  " +
                        "  SELECT  a.许可, b.来源, b.品牌, b.级别,   " +
                        "    count(1) 累计可开票订单数  " +
                        "  FROM VW_ORDER_QUOTATION a  " +
                        "  join LSH车型 b ON a.车型编号 = b.车型编号   " +
                        "  WHERE  date_format(a.订单签署日期,'%Y-%m-%d') <= '2020-01-01'  " +
                        "  AND (A.开票时间 IS NULL OR date_format(A.开票时间,'%Y-%m-%d') > '2020-01-01') " +
                        "  AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
                        "  GROUP BY a.许可,b.来源,b.品牌,b.级别  " +
                        ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  ";
        {
            metas.add(new BIExtractMetaData("totalInvoicableUnfulfilledOrderWithExistStock", "可开票订单数", totalInvoicableUnfulfilledOrderWithExistStock).addExtractColumnMetaData(new BIExtractColumnMetaData("累计可开票订单数", "total_invoicable_order_num")));
        }

        //当月可开票订单数(OK)
        String invoicableUnfulfilledOrderWithExistStock =
                "SELECT allset.*,IFNULL(a.可开票订单数,0) AS 可开票订单数 FROM   " +
                        " kpi_allset allset  " +
                        "LEFT JOIN   " +
                        "(  " +
                        "  SELECT  a.许可, b.来源, b.品牌, b.级别,   " +
                        "    count(1) 可开票订单数  " +
                        "  FROM VW_ORDER_QUOTATION a  " +
                        "  join LSH车型 b ON a.车型编号 = b.车型编号   " +
                        "  WHERE  (date_format(a.预计开票日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.预计开票日期,'%Y-%m-%d') >= '2000-01-01')  " +
                        "  AND A.订单审批通过日期 <= '2020-01-01' " +
                        "  AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
                        "  GROUP BY a.许可,b.来源,b.品牌,b.级别  " +
                        ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  ";
        {
            metas.add(new BIExtractMetaData("invoicableUnfulfilledOrderWithExistStock", "可开票订单数", invoicableUnfulfilledOrderWithExistStock).addExtractColumnMetaData(new BIExtractColumnMetaData("可开票订单数", "invoicable_order_num")));
        }

        String clueNum = "SELECT allset.*,IFNULL(a.散客线索数,0)+IFNULL(b.导入线索数,0) AS 新增线索数  " +
                " FROM   " +
                " kpi_allset allset  " +
                "LEFT JOIN  " +
                "(  " +
                "  SELECT  " +
                "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                "    COUNT(DISTINCT(a.`顾客编号`)) AS 散客线索数  " +
                "  FROM  " +
                "    `顾客进店记录表` a,  " +
                "    lsh车型 b  " +
                "  WHERE  " +
                "    a.`是否首次到店` = '是'  " +
                "  AND a.`顾客来源` = '散客'  " +
                "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                "  AND a.`意向车型A` = b.`车型编号`  " +
                "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                "LEFT JOIN  " +
                "(  " +
                "  SELECT  " +
                "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                "    COUNT(DISTINCT(CONCAT(a.`许可`,a.`电话号码`))) AS 导入线索数  " +
                "  FROM  " +
                "    lsh导入名单 a,  " +
                "    lsh车型 b  " +
                "  WHERE  " +
                "    a.`是否线索` = '是'  " +
                "  AND a.`是否有效线索` = '是'  " +
                "  AND a.`导入日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                "  AND a.`导入日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                "  AND a.`车型编号`=b.`车型编号`  " +
                "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                ") b ON b.`许可`=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`  ";
        {
            metas.add(new BIExtractMetaData("clueNum", "线索数", clueNum).addExtractColumnMetaData(new BIExtractColumnMetaData("新增线索数", "new_clue_num")));
        }

        // 新增线索进店转化率(OK,((MGT统计开始日<=进店日期<=MGT统计结束日)的首次进店且未战败的线索数)/新增线索数)
        String newClueEnteringStoreConversionRate =
                "SELECT allset.*,IFNULL(c.未战败线索数,0) AS 首次进店且未战败的线索数  " +
                        " FROM   " +
                        " kpi_allset allset  " +
                        "LEFT JOIN  " +
                        "(  " +
                        "  SELECT  " +
                        "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                        "    COUNT(DISTINCT(a.`顾客编号`)) AS 未战败线索数  " +
                        "  FROM  " +
                        "    `顾客进店记录表` a,  " +
                        "    `lsh车型` b,  " +
                        "    `顾客记录汇总` c  " +
                        "  WHERE  " +
                        "    a.`是否首次到店` = '是'  " +
                        "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                        "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                        "  AND a.`意向车型A` = b.`车型编号`  " +
                        "  AND a.`顾客编号` = c.`顾客编号`  " +
                        "  AND NOT EXISTS   " +
                        "  (  " +
                        "    SELECT  " +
                        "      *  " +
                        "    FROM  " +
                        "      `跟进记录表` follow  " +
                        "    WHERE  " +
                        "      follow.`许可` = c.`许可`  " +
                        "    AND follow.`电话号码` = c.`电话号码`  " +
                        "    AND follow.`跟进日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                        "    AND follow.`跟进日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                        "  )  " +
                        "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                        ") c ON c.`许可`=allset.许可_BMBS AND c.`来源`=allset.`来源` AND c.`品牌`=allset.`品牌` AND c.`级别`=allset.`级别`";
        {
            metas.add(new BIExtractMetaData("newClueEnteringStoreConversionRate", "首次进店且未战败的线索数", newClueEnteringStoreConversionRate).addExtractColumnMetaData(new BIExtractColumnMetaData("首次进店且未战败的线索数", "first_enter_undefeated_clue_num")));
        }

        // 未关闭线索的再次进店转化率(OK,同新增线索进店转化率)
        String unclosedClueReEnteringStoreConversionRate =
                "SELECT allset.*,IFNULL(c.未战败线索数,0) AS 再次进店且未战败的线索数  " +
                        " FROM   " +
                        " kpi_allset allset  " +
                        "LEFT JOIN  " +
                        "(  " +
                        "  SELECT  " +
                        "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                        "    COUNT(DISTINCT(a.`顾客编号`)) AS 未战败线索数  " +
                        "  FROM  " +
                        "    `顾客进店记录表` a,  " +
                        "    `lsh车型` b,  " +
                        "    `顾客记录汇总` c  " +
                        "  WHERE  " +
                        "    a.`是否首次到店` = '否'  " +
                        "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                        "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                        "  AND a.`意向车型A` = b.`车型编号`  " +
                        "  AND a.`顾客编号` = c.`顾客编号`  " +
                        "  AND NOT EXISTS   " +
                        "  (  " +
                        "    SELECT  " +
                        "      *  " +
                        "    FROM  " +
                        "      `跟进记录表` follow  " +
                        "    WHERE  " +
                        "      follow.`许可` = c.`许可`  " +
                        "    AND follow.`电话号码` = c.`电话号码`  " +
                        "    AND follow.`跟进结果` = '新车战败'  " +
                        "    AND follow.`跟进日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                        "    AND follow.`跟进日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                        "  )  " +
                        "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                        ") c ON c.`许可`=allset.许可_BMBS AND c.`来源`=allset.`来源` AND c.`品牌`=allset.`品牌` AND c.`级别`=allset.`级别`";
        {
            metas.add(new BIExtractMetaData("unclosedClueReEnteringStoreConversionRate", "再次进店且未战败的线索数", unclosedClueReEnteringStoreConversionRate).addExtractColumnMetaData(new BIExtractColumnMetaData("再次进店且未战败的线索数", "reenter_undefeated_clue_num")));
        }

        // 客户重新分配比例(OK,重新分配只有mgt操作的数据，现订现交需要反推逻辑)
        String customerReassignment =
                "SELECT  " +
                        "  allset.*,IFNULL(a.重新分配线索数,0) AS 重新分配线索数  " +
                        "FROM  " +
                        "  kpi_allset allset  " +
                        "LEFT JOIN  " +
                        "(  " +
                        "  SELECT  " +
                        "    b.`许可`,c.`来源`,c.`品牌`,c.`级别`,COUNT(1) AS 重新分配线索数  " +
                        "  FROM  " +
                        "    custom_reassign_log a ,  " +
                        "    `顾客记录汇总` b,  " +
                        "    `lsh车型` c  " +
                        "  WHERE  " +
                        "    DATE_FORMAT(a.reassign_datetime , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')  " +
                        "  AND DATE_FORMAT(a.reassign_datetime , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')  " +
                        "  AND a.custom_code = b.`顾客编号`  " +
                        "  AND b.`意向车型A` = c.`车型编号`  " +
                        "  GROUP BY b.`许可`,c.`来源`,c.`品牌`,c.`级别`  " +
                        ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  ";
        {
            metas.add(new BIExtractMetaData("customerReassignment", "重新分配线索数", customerReassignment).addExtractColumnMetaData(new BIExtractColumnMetaData("重新分配线索数", "reassign_clue_num")));
        }

        // 试乘试驾率(OK)
        String testDriveNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  b.来源, b.品牌, b.级别, " +
                "       试乘试驾数 " +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "      SELECT  " +
                "       a.`许可`, " +
                "       car.`来源`, " +
                "       car.`品牌`, " +
                "       car.`级别`, " +
                "       COUNT(1)  `试乘试驾数` " +
                "      FROM `顾客进店记录表` a ,  " +
                "      `lsh车型` car," +
                "   (    " +
                "      SELECT MIN(a.`进店编号`) `进店编号`   " +
                "      FROM `顾客进店记录表` a    " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` " +
                "      WHERE a.`进店日期` >=  '2000-01-01'  AND a.`进店日期` <=  '2020-01-01'  " +
                "      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')  " +
                "      AND IFNULL(`停留时间`, 0 ) > 0  " +
                "      AND cust.`顾客姓名`!='' " +
                "      AND cust.`电话号码`!='' " +
                "      AND car.`车型`!='' " +
                "      AND a.`客户级别` NOT LIKE 'E%' " +
                "      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))  " +
                "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE car.品牌 = 'MB' END) " +
                "      AND EXISTS (SELECT `进店编号` FROM `顾客试驾记录` tdrive, `lsh车型` tcar  WHERE tdrive.`进店编号`=a.`进店编号` AND tdrive.`试驾车型编号` = tcar.`车型编号` AND tcar.`车型`!='' ) " +
                "      GROUP BY a.`顾客编号`   " +
                "   ) sub   " +
                "   WHERE a.`进店编号` = sub.`进店编号` AND a.`意向车型A` = car.`车型编号`    " +
                "   GROUP BY a.`许可` , car.`来源` , car.`品牌` , car.`级别`" +
                "  ) B ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

        metas.add(new BIExtractMetaData("testDriveNum", "试乘试驾", testDriveNum).addExtractColumnMetaData(new BIExtractColumnMetaData("试乘试驾数", "test_drive_num")));

        // 报价率(OK)
        // 报价率(OK)
        String quotationNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
                "    报价数 " +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "      SELECT  " +
                "       a.`许可`, " +
                "       car.`来源`, " +
                "       car.`品牌`, " +
                "       car.`级别`, " +
                "       COUNT(1) 报价数 " +
                "      FROM `顾客进店记录表` a ,  " +
                "      `lsh车型` car," +
                "   (    " +
                "      SELECT MIN(a.`进店编号`) `进店编号`   " +
                "      FROM `顾客进店记录表` a    " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` " +
                "      WHERE a.`进店日期` >=  '2000-01-01'  AND a.`进店日期` <=  '2020-01-01'  " +
                "      AND IFNULL(`停留时间`, 0 ) > 0  " +
                "      AND cust.`顾客姓名`!='' " +
                "      AND cust.`电话号码`!='' " +
                "      AND car.`车型`!='' " +
                "      AND a.`客户级别` NOT LIKE 'E%' " +
                "      AND a.`是否报价` = '是' " +
                "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE car.品牌 = 'MB' END) " +
                "      GROUP BY a.`顾客编号`   " +
                "   ) sub   " +
                "   WHERE a.`进店编号` = sub.`进店编号` AND a.`意向车型A` = car.`车型编号`    " +
                "      GROUP BY a.`许可` , car.`来源` , car.`品牌` , car.`级别`" +
                "  ) B ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";
        {
            metas.add(new BIExtractMetaData("quotationNum", "报价数", quotationNum).addExtractColumnMetaData(new BIExtractColumnMetaData("报价数", "quotation_num")));
        }

        String showroomTraffic = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
                "   进店线索数 " +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "       SELECT   " +
                "           许可, 来源, 品牌, 级别, SUM(`首次进店线索数`) + SUM(`再次进店线索数`) `进店线索数` " +
                "       FROM (    " +
                "	        SELECT a.许可, 来源, 品牌, 级别, COUNT(1) `首次进店线索数`,  0  `再次进店线索数` " +
                "	        FROM `顾客进店记录表` a   " +
                "	        LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "	        LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "	        WHERE a.`进店日期` >=   '2000-01-01'  AND a.`进店日期` <=   '2020-01-01'   " +
                "	            AND a.`是否首次到店` = '是'  " +
                "	            AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')   " +
                "	            AND IFNULL(`停留时间`, 0 ) > 0   " +
                "	            AND a.`顾客来源` IN ('散客','呼入','网络','主动集客','推荐','再购')  " +
                "	            AND cust.`顾客姓名`!=''  " +
                "	            AND cust.`电话号码`!=''  " +
                "	            AND car.`车型`!=''  " +
                "	            AND a.`客户级别` NOT LIKE 'E%'  " +
                "	            AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))   " +
                "           GROUP BY a.许可, 来源, 品牌, 级别    " +
                "	        UNION ALL  " +
                "	        SELECT  a.许可, 来源, 品牌, 级别, 0 `首次进店线索数`,   COUNT(1) `再次进店线索数` " +
                "	        FROM `顾客进店记录表` a ,  " +
                "           `lsh车型` car," +
                "       (    " +
                "           SELECT MIN(a.`进店编号`) `进店编号`   " +
                "           FROM `顾客进店记录表` a    " +
                "	        LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "	        LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "	        WHERE a.`进店日期` >=   '2000-01-01'  AND a.`进店日期` <=   '2020-01-01'   " +
                "	            AND a.`是否首次到店` = '否'  " +
                "	            AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')   " +
                "	            AND IFNULL(`停留时间`, 0 ) > 0   " +
                "	            AND a.`顾客再次来源` IN ('活跃','休眠')  " +
                "	            AND cust.`顾客姓名`!=''  " +
                "	            AND cust.`电话号码`!=''  " +
                "	            AND car.`车型`!=''  " +
                "	            AND a.`客户级别` NOT LIKE 'E%'  " +
                "	            AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))  " +
                "           GROUP BY a.`顾客编号`   " +
                "       ) sub   " +
                "       WHERE a.`进店编号` = sub.`进店编号` AND a.`意向车型A` = car.`车型编号`    " +
                "           GROUP BY a.许可, 来源, 品牌, 级别    " +
                "       ) aa   " +
                "       where (case when 'MB' = '' then true else aa.品牌 = 'MB' end)  " +
                "       GROUP BY 许可, 品牌, 级别 " +
                "   ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";
        {
            metas.add(new BIExtractMetaData("showroomTraffic", "展厅客流量LSH", showroomTraffic).addExtractColumnMetaData(new BIExtractColumnMetaData("进店线索数", "inroom_clue_num")));
        }

        // 线索统计
        String clueNUm = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  B.来源, b.品牌, b.级别, " +
                "   SUM(线索数) 线索数 " +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "       SELECT A.许可,B.来源, B.品牌, B.级别, count(1) 线索数 " +
                "       FROM `顾客记录汇总` a  LEFT JOIN lsh车型 b ON a.`意向车型A`=b.车型编号 " +
                "       WHERE a.`顾客状态` != '战败' " +
                "       GROUP BY A.许可,B.来源, B.品牌, B.级别 " +
                "       UNION ALL" +
                "       SELECT A.许可,B.来源, B.品牌, B.级别, count(1) 线索数 " +
                "       FROM `lsh导入名单` a  LEFT JOIN lsh车型 b ON a.`车型编号`=b.车型编号 " +
                "       WHERE a.`是否线索` = '是' AND a.`是否有效线索` = '是'  " +
                "             AND a.`线索创建日期`>=date_sub( date_sub( date_format('2000-01-01', '%Y-%m-%d'), INTERVAL extract(DAY FROM '2000-01-01') - 1 DAY ), INTERVAL 2 MONTH )  " +
                "             AND NOT EXISTS( SELECT c.`电话号码` FROM `顾客记录汇总` c " +
                "                      WHERE( c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) OR (c.`顾客状态`='战败' AND a.`线索创建日期`>IFNULL('1970-01-01',c.战败日期))) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码`)  " +
                "       GROUP BY A.许可,B.来源, B.品牌, B.级别 " +
                "    ) B ON a.许可_BMBS = b.许可" +
                "    GROUP BY A.许可_BMBS, B.级别";

        {
            metas.add(new BIExtractMetaData("clueNUm", "线索统计", clueNUm).addExtractColumnMetaData(new BIExtractColumnMetaData("线索数", "clue_num")));
        }

        // 新增交车数(OK)
        String deliveryNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
                "   交车数 " +
                "   FROM lsh经销商表 a  " +
                "   LEFT JOIN (  " +
                "      SELECT   " +
                "        a.`许可`,  " +
                "        car.`来源`,  " +
                "        car.`品牌`,  " +
                "        car.`级别`,  " +
                "        SUM(IF(a.`是否交车`='是',1,0)) `交车数`  " +
                "      FROM `顾客进店记录表` a   " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "      WHERE a.`进店日期` >=  '2000-01-01'  AND a.`进店日期` <=  '2020-01-01'   " +
                "      AND IFNULL(`停留时间`, 0 ) > 0   " +
                "      AND cust.`顾客姓名`!=''  " +
                "      AND cust.`电话号码`!=''  " +
                "      AND car.`车型`!=''  " +
                "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE car.品牌 = 'MB' END)  " +
                "      GROUP BY a.`许可` , car.`来源` , car.`品牌` , car.`级别`" +
                "   ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";
        {
            metas.add(new BIExtractMetaData("deliveryNum", "交车数", deliveryNum).addExtractColumnMetaData(new BIExtractColumnMetaData("交车数", "delivery_num")));
        }

        // 过去三个月已零售(OK)
        String averageSaleVolumn = " select z.*, CEIL(前三个月平均销量) 前三个月平均销量    " +
                " from kpi_allset z   " +
                " left join (     " +
                "        select sum(CASE    " +
                "                    WHEN date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')       " +
                "                      and date_format(a.开票时间,'%Y-%m-%d')  <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')    " +
                "                      AND  date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')     " +
                "                      and date_format(a.退票时间,'%Y-%m-%d')  <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')    " +
                "                      AND a.退票时间 > a.开票时间 THEN 0   " +
                "                    WHEN date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')  " +
                "                      and date_format(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d') THEN 1   " +
                "                    WHEN date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')       " +
                "                      and  date_format(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d') THEN -1   " +
                "                    END) / 3 as '前三个月平均销量', a.许可 ,来源,e.品牌,e.级别     " +
                "         from vw_retailed_quotation a          " +
                "         join LSH车型 e on a.车型编号 = e.车型编号   " +
                "         join 报价订单表 c ON a.订单编号 = c.订单编号  " +
                "         where  ((date_format(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')  AND date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01'))      " +
                "                OR (date_format(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')  AND date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')))  " +
                "            AND a.订单编号 = c.订单编号  " +
                "            AND c.销售类型 not like 'B%'  " +
                "            AND c.`销售类型`not like 'I%'      " +
                "            and a.车辆零售状态 = '已卖'      " +
                "            and (case when 'MB' = '' then true else e.品牌 = 'MB' end)       " +
                "          group by a.许可,来源,e.品牌,e.级别      " +
                "    ) b on z.许可_BMBS = b.许可  AND B.`来源`=z.`来源` AND B.`品牌`=z.`品牌` AND B.`级别`=z.`级别`";
        {
            metas.add(new BIExtractMetaData("averageSaleVolumn", "过去三个月已零售", averageSaleVolumn).addExtractColumnMetaData(new BIExtractColumnMetaData("前三个月平均销量", "average_sale_volumn")));
        }

        // 其他业务收入毛利率不含二手车续保(OK)   测试完成  偏大 0.6%   经销商计算规则少上牌服务费，其他服务费，新保手续费，五星延保毛利
        String gpForOtherIncome = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
                "   a.salesCode, a.来源,a.品牌, a.级别,     " +
                "   SUM(VEHICLE_OTHER_INCOME.TYPE * (精品销售毛利未税 + 其他服务费未税 + 上牌服务费未税 + 置换评估费未税 +  金融服务费未税 + 金融手续费未税 + 保险手续费未税 + 报价预收其他毛利 + 延保销售毛利)) + " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 其他收入," +
                "   IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 其他收入品牌调整值 " +
                "   FROM kpi_allset a " +
                "   LEFT JOIN (  " +
                "        SELECT a.许可, d.来源, d.品牌,d.级别,  " +
                "               a.开票类型 TYPE,  " +
                "            	IFNULL(h.上牌服务费, 0) / 1.17 AS 上牌服务费未税,   " +
                "           	IFNULL(h.其他服务费,0) / 1.17 AS 其他服务费未税,      " +
                "           	IFNULL(i.金融服务费,0) / 1.17 AS 金融服务费未税,     " +
                "           	IFNULL(i.金融手续费,0) / 1.06 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.06 AS 金融手续费未税,      " +
                "           	IFNULL(j.交强险手续费,0) / 1.06 + IFNULL(j.商业险手续费,0) / 1.06 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.06 AS 保险手续费未税,    " +
                "           	IFNULL(k.置换评估费,0) / 1.17 AS 置换评估费未税,      " +
                "               IFNULL(报价预收其他毛利,0)/1.17 报价预收其他毛利,  " +
                "               IFNULL(延保销售毛利,0)/1.17 延保销售毛利 , " +
                "               IFNULL(ll.精品销售毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 AS 精品销售毛利未税    " +
                "        FROM vw_retail_invoice_info a  " +
                "        LEFT JOIN (      " +
                "            SELECT a.编号, SUM(IFNULL(a.`折后价` * 数量, 0)) - SUM(IFNULL(a.成本 * 数量, 0)) AS 精品销售毛利  " +
                "            FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`     " +
                "        ) ll ON ll.编号 = a.精品      " +
                "        LEFT JOIN (      " +
                "            SELECT 费用编号, " +
                "                   SUM(IF(费用名称 = '其他服务费', 费用折后价,0)) AS 其他服务费,        " +
                "                   SUM(IF(费用名称 = '上牌服务费', 费用折后价,0) - IF(费用名称 = '上牌服务费成本', 费用原价,0)) AS 上牌服务费        " +
                "           FROM 报价费用详细表  " +
                "           GROUP BY 费用编号        " +
                "        ) h ON h.费用编号 = a.费用编号    " +
                "        LEFT JOIN ( " +
                "            SELECT 预收编号, IFNULL(其他一,0) + IFNULL(其他二,0) + IFNULL(其他三,0) + IFNULL(其他四,0) + IFNULL(其他五,0) - IFNULL(其他一成本,0) - IFNULL(其他二成本,0)  报价预收其他毛利  " +
                "            FROM 报价预收表  " +
                "            ) 报价预收 ON 报价预收.预收编号 = a.预收编号  " +
                "        LEFT JOIN ( " +
                "            SELECT 延保编号, IF(IFNULL(厂家延保实收,0)>0, IFNULL(厂家延保实收,0) - IFNULL(厂家延保MSRP,0), 0) + IF(IFNULL(第三方延保实收,0)>0,  IFNULL(第三方延保实收,0)- IFNULL(第三方延保MSRP,0),0) AS 延保销售毛利 " +
                "            FROM 报价五星延保 " +
                "       ) 报价延保 ON 报价延保.延保编号 = a.延保编号 " +
                "        LEFT JOIN 报价金融表 i ON i.金融编号 = a.金融      " +
                "        LEFT JOIN 报价保险表 j ON j.保险编号 = a.保险编号    " +
                "        LEFT JOIN 报价置换表 k ON k.置换编号 = a.置换编号      " +
                "        LEFT JOIN (  " +
                "            SELECT PERMIT, STOCK_CODE,   " +
                "                   SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "                   SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
                "                   SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "            FROM INCOME_ADJUSTMENT   " +
                "            WHERE TYPE = 1  " +
                "            GROUP BY PERMIT, STOCK_CODE " +
                "        ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
                "        ,LSH车型 d    " +
                "        WHERE  (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')  " +
                "           AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)  " +
                "           AND a.车型编号 = d.车型编号    " +
                "    ) VEHICLE_OTHER_INCOME ON a.许可_BMBS = VEHICLE_OTHER_INCOME.许可 AND a.来源 = VEHICLE_OTHER_INCOME.来源 AND a.品牌 = VEHICLE_OTHER_INCOME.品牌 AND  a.级别 = VEHICLE_OTHER_INCOME.级别  " +
                "    LEFT JOIN (" +
                "         SELECT PERMIT, 来源, 品牌, 级别,   " +
                "                SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "                SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
                "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "         FROM INCOME_ADJUSTMENT, LSH车型   " +
                "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "               AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "               AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "               AND '2000-01-01' <> '2020-01-01' " +
                "         GROUP BY PERMIT, 来源, 品牌, 级别  " +
                "    ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                "    LEFT JOIN (" +
                "         SELECT PERMIT, 来源, 品牌,  " +
                "                SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "                SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
                "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "         FROM INCOME_ADJUSTMENT, LSH车型   " +
                "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "           AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "           AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "           AND '2000-01-01' <> '2020-01-01' " +
                "         GROUP BY PERMIT, 来源, 品牌  " +
                "    ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
        {
            metas.add(new BIExtractMetaData("gpForOtherIncome", "其他业务收入毛利", gpForOtherIncome).addExtractColumnMetaData(new BIExtractColumnMetaData("其他收入", "其他收入品牌调整值", "other_business_gross_amount")));
        }

        // LSH5星延保渗透率 + LSH5星延保毛利率 (OK) (CHECKED)
        String extendWarranty = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
                " a.salesCode, a.来源,a.品牌, a.级别, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 五星延保销售台数) 延保销售台数, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 自店延保台数) 自店延保台数, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 厂家延保台数) 厂家延保台数, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 第三方延保台数) 第三方延保台数, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 延保销售毛利) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保销售毛利, " +
                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 延保销售营业额) 延保销售营业额, " +

                " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 自店延保销售毛利) 自店延保销售毛利, " +
                " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保毛利品牌成本调整 " +
                " FROM kpi_allset a " +
                " LEFT JOIN ( " +
                "           SELECT  a.许可, d.来源, d.品牌, d.级别,   " +
                "               a.开票类型 TYPE , " +
                "               IFNULL(b.延保销售毛利, 0)/1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保销售毛利,  " +
                "               IFNULL(b.延保销售营业额, 0)/1.17  延保销售营业额,  " +
                "               IF(B.自店延保销售毛利 IS NOT NULL, 1, 0) 自店延保销售毛利,  " +
                "               B.五星延保销售台数, B.第三方延保台数,  B.厂家延保台数, B.自店延保台数" +
                "          FROM vw_retail_invoice_info a " +
                "          LEFT JOIN (  " +
                "              SELECT PERMIT, STOCK_CODE,   " +
                "                     SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
                "              FROM INCOME_ADJUSTMENT   " +
                "              WHERE TYPE = 1  AND IS_GIVE = 0 " +
                "              GROUP BY PERMIT, STOCK_CODE " +
                "          ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
                "          LEFT JOIN (        " +
                "		      SELECT 延保编号, IFNULL(延保原价,0) + IFNULL(厂家延保MSRP,0) + IFNULL(第三方延保MSRP,0) 延保原价 ," +
                "                             IF(IFNULL(厂家延保实收,0)>0, IFNULL(厂家延保实收,0) - IFNULL(厂家延保MSRP,0), 0) + IF(IFNULL(第三方延保实收,0)>0,  IFNULL(第三方延保实收,0)- IFNULL(第三方延保MSRP,0),0) AS 延保销售毛利,        " +
                "                             IF(IFNULL(厂家延保实收,0)>0, IFNULL(厂家延保实收,0), 0)+ IF(IFNULL(第三方延保实收,0)>0, IFNULL(第三方延保实收,0),0) AS 延保销售营业额,    " +
                "		                      (IFNULL(延保折后价,0) - IFNULL(延保原价,0)) AS 自店延保销售毛利,        " +
                "		                      IF(IFNULL(延保折后价,0)>0,1,0) + IF(IFNULL(厂家延保实收,0)>0,1,0) + IF(IFNULL(第三方延保实收,0)>0,1,0) AS 五星延保销售台数,        " +
                "		                      IF(IFNULL(延保折后价,0) > 0, 1, 0) AS 自店延保台数,        " +
                "		                      IF(IFNULL(厂家延保实收,0) > 0, 1, 0) AS 厂家延保台数,        " +
                "		                      IF(IFNULL(第三方延保实收,0) > 0, 1, 0) AS 第三方延保台数        " +
                "        	  FROM 报价五星延保   " +
                "          ) b ON b.延保编号 = a.延保编号,         " +
                "          LSH车型 d,  报价订单表 e " +
                "          WHERE a.车型编号 = d.车型编号 AND a.订单编号 = e.订单编号 " +
                "            AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')     " +
                "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)" +
                "   ) VEHICLE_FIVE_STAR_EXTEND ON a.许可_BMBS = VEHICLE_FIVE_STAR_EXTEND.许可 AND a.来源 = VEHICLE_FIVE_STAR_EXTEND.来源 AND a.品牌 = VEHICLE_FIVE_STAR_EXTEND.品牌 AND  a.级别 = VEHICLE_FIVE_STAR_EXTEND.级别" +
                "   LEFT JOIN (" +
                "       SELECT PERMIT, 来源, 品牌, 级别,   " +
                "              SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
                "       FROM INCOME_ADJUSTMENT, LSH车型   " +
                "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "             AND '2000-01-01' <> '2020-01-01' " +
                "       GROUP BY PERMIT, 来源, 品牌, 级别  " +
                "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                "   LEFT JOIN (" +
                "       SELECT PERMIT, 来源, 品牌,  " +
                "              SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
                "       FROM INCOME_ADJUSTMENT, LSH车型   " +
                "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "             AND '2000-01-01' <> '2020-01-01' " +
                "       GROUP BY PERMIT, 来源, 品牌  " +
                "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
        {
            metas.add(new BIExtractMetaData("extendWarranty", "延保", extendWarranty).addExtractColumnMetaData(new BIExtractColumnMetaData("延保销售台数", "extended_warranty_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("延保销售毛利", "延保毛利品牌成本调整", "extended_warranty_gross_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("延保销售营业额", "extended_warranty_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("自店延保销售毛利", "dealer_extended_warranty_gross_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("自店延保台数", "dealer_extended_warranty_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("厂家延保台数", "factory_extended_warranty_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("第三方延保台数", "other_extended_warranty_num"))
            );
        }

        //  盗抢险和玻璃箱台数统计
        String specialProductionNum = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, 来源,  b.品牌, b.级别, " +
                "  b.盗抢险台数, b.玻璃险台数 " +
                "  FROM LSH经销商表 a LEFT JOIN (" +
                "       SELECT b.许可, e.来源, e.品牌, e.级别, sum(IF(d.险种 = '全车盗抢险', 1, 0)) 盗抢险台数, sum(IF(d.险种 = '玻璃单独破碎险', 1, 0)) 玻璃险台数 " +
                "       FROM LSH保险申请附属 a, 报价单表 B, vw_max_quotedcode_all c, 报价保险详细表 d, `lsh车型` e  " +
                "       WHERE A.初始报价单号 = b.初始报价单号 and b.报价编号 = c.报价编号 AND b.保险编号 = d.保险编号 and b.车型编号 = e.车型编号 " +
                "        and a.商业险出单日期 >= '2000-01-01'  " +
                "        and a.商业险出单日期 <= '2020-01-01' " +
                "       group by b.许可, e.来源, e.品牌, e.级别) b on a.许可_BMBS = b.许可 " +
                "       where a.`是否测试` = '否' ";
        {
            metas.add(new BIExtractMetaData("specialProductionNum", "盗抢险和玻璃险台数统计", specialProductionNum)
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("盗抢险台数", "theft_robbery_insurance_vehicle_num"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("玻璃险台数", "glass_robbery_insurance_vehicle_num"))
            );
        }

        // 其他服务费收入(OK)
        String saleOtherFee = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,  " +
                " 上牌服务费, 其他服务费, (上牌服务费 + 其他服务费) 后市场服务费收入   " +
                " FROM LSH经销商表 a  LEFT JOIN (" +
                "   SELECT a.许可, 来源, a.品牌, a.级别, " +
                "          SUM(TYPE * 上牌服务费) 上牌服务费, " +
                "          SUM(TYPE * 其他服务费) 其他服务费 " +
                "   FROM (" +
                "	       SELECT  a.许可, 来源, c.品牌, c.级别, " +
                "              a.开票类型 TYPE, " +
                "              b.上牌服务费 / 1.17 上牌服务费, " +
                "              b.其他服务费 / 1.17 其他服务费 " +
                "          FROM vw_retail_invoice_info a " +
                "          LEFT JOIN ( " +
                "               SELECT 费用编号, " +
                "               SUM(IF(费用名称 = '其他服务费', 费用折后价,0)) AS 其他服务费,        " +
                "               SUM(IF(费用名称 = '上牌服务费', 费用折后价,0) - IF(费用名称 = '上牌服务费成本', 费用原价,0)) AS 上牌服务费        " +
                "               FROM 报价费用详细表  " +
                "               GROUP BY 费用编号        " +
                "	       ) b ON b.费用编号 = a.费用编号, LSH车型 c  " +
                "          WHERE a.车型编号 = c.车型编号 " +
                "            AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')     " +
                "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END)  " +
                "   ) a GROUP BY a.许可, 来源, 品牌, a.级别 " +
                " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'";
        {
            metas.add(new BIExtractMetaData("saleOtherFee", "其他服务费收入", saleOtherFee).addExtractColumnMetaData(new BIExtractColumnMetaData("上牌服务费", "vehicle_license_service_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("其他服务费", "vehicle_other_service_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("后市场服务费收入", "after_market_service_fee_income"))
            );
        }

        // 后市场产品毛利率 + 后市场产品单车营业额 (OK)
        String afterMarketProduct = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
                " a.salesCode, a.来源,a.品牌, a.级别,  " +
                " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * 后市场产品营业额) 后市场产品营业额, " +
                " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * (后市场产品营业额 - 后市场产品成本)) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 后市场产品销售毛利, " +
                " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 后市场产品品牌成本调整   " +
                " FROM kpi_allset a  " +
                " LEFT JOIN (" +
                "      SELECT  a.许可, c.来源, c.品牌, c.级别, " +
                "          a.开票类型 TYPE, " +
                "          1 新车销量, " +
                "          IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,      " +
                "          IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
                "          b.后市场产品营业额 / 1.17 后市场产品营业额, " +
                "          (b.后市场产品成本 / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17)  后市场产品成本 " +
                "      FROM vw_retail_invoice_info a " +
                "      LEFT JOIN ( " +
                "		  SELECT 编号, SUM(IFNULL(`折后价` * 数量, 0)) 后市场产品营业额, SUM(IFNULL(成本,0)* 数量) 后市场产品成本     " +
                "		  FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`     " +
                "	   ) b ON b.编号 = a.精品  " +
                "      LEFT JOIN (  " +
                "         SELECT PERMIT, STOCK_CODE,   " +
                "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "         FROM INCOME_ADJUSTMENT   " +
                "         WHERE TYPE = 1  " +
                "         GROUP BY PERMIT, STOCK_CODE " +
                "      ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
                "      , LSH车型 c " +
                "      WHERE a.车型编号 = c.车型编号 " +
                "            AND (date_format(a.处理时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.处理时间,'%Y-%m-%d') >= '2000-01-01')    " +
                " ) VEHICLE_AFTERMARKET_INFO ON a.许可_BMBS = VEHICLE_AFTERMARKET_INFO.许可 AND a.来源 = VEHICLE_AFTERMARKET_INFO.来源 AND a.品牌 = VEHICLE_AFTERMARKET_INFO.品牌 AND  a.级别 = VEHICLE_AFTERMARKET_INFO.级别 " +
                " LEFT JOIN (" +
                "      SELECT PERMIT, 来源, 品牌, 级别,   " +
                "             SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "            AND '2000-01-01' <> '2020-01-01' " +
                "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
                " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                " LEFT JOIN (" +
                "      SELECT PERMIT, 来源, 品牌,  " +
                "             SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
                "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "            AND '2000-01-01' <> '2020-01-01' " +
                "      GROUP BY PERMIT, 来源, 品牌  " +
                " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
        {
            metas.add(new BIExtractMetaData("afterMarketProduct", "后市场产品", afterMarketProduct).addExtractColumnMetaData(new BIExtractColumnMetaData("后市场产品营业额", "after_market_product_amount"))
                    .addExtractColumnMetaData(new BIExtractColumnMetaData("后市场产品销售毛利", "后市场产品品牌成本调整", "after_market_product_gross_amount"))
            );
        }
    }

    public List<BIExtractMetaData> getMetaDatas() {
        this.metas.clear();
        buildStatements();
        return metas;
    }
}
